/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.pay.repository;

import me.zhengjie.business.domain.sup.Gsgl;
import me.zhengjie.business.domain.vo.KB03;
import me.zhengjie.business.domain.vo.KB22;
import me.zhengjie.pay.domain.QueryRefundVo;
import me.zhengjie.pay.domain.SettleAccount;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
* @website https://el-admin.vip
* @author xiahongbo
* @date 2022-08-02
**/
public interface SettleAccountRepository extends JpaRepository<SettleAccount, String>, JpaSpecificationExecutor<SettleAccount> {

    @Query(
            value = "select t3.bis_zhsq_id, " +
                    "       t3.bis_zhsq_snry_xm, " +
                    "       t3.bis_zhsq_snry_sfz, " +
                    "       t3.bis_zhsq_jhrxm, " +
                    "       t3.bis_zhsq_jhrdh, " +
                    "       to_char(t3.bis_zhsq_dykkrq, 'yyyy-mm-dd') bis_zhsq_dykkrq, " +
                    "       to_char(t3.bis_zhsq_dyjsrq, 'yyyy-mm-dd') bis_zhsq_dyjsrq, " +
                    "       t2.bis_jzdj_lsh, " +
                    "       t4.bis_order_hlrq, " +
                    "       t4.bis_order_hly_id, " +
                    "       t4.bis_order_hly_name, " +
                    "       t1.pay_js_tran_flow_no, " +
                    "       t1.pay_js_fee, " +
                    "       t1.pay_js_cash_amt, " +
                    "       t1.pay_js_first_amt, " +
                    "       t1.pay_js_trade_time, " +
                    "       t1.pay_refund_stas " +
                    "  from PAY_JS t1, BIS_JZDJ t2, BIS_ZHSQ t3, BIS_ORDER t4 " +
                    " where t1.pay_js_visit_id = t2.bis_jzdj_lsh " +
                    "   and t1.pay_js_patient_id = t3.bis_zhsq_id " +
                    "   and t2.bis_jzdj_order_id = t4.bis_order_id ",
            nativeQuery = true)
    List<Object> refundList(String blurry, Pageable pageable);


    @Query(value = "select new me.zhengjie.pay.domain.QueryRefundVo(" +
            "t3.bisZhsqId, " +
            "t3.bisZhsqSnryXm, " +
            "t3.bisZhsqSnrySfz, " +
            "t3.bisZhsqJhrxm, " +
            "t3.bisZhsqJhrdh, " +
            "t3.bisZhsqDykkrq, " +
            "t3.bisZhsqDyjsrq, " +
            "t2.bisJzdjLsh, " +
            "t4.bisOrderHlrq, " +
            "t4.bisOrderHlyId, " +
            "t4.bisOrderHlyName, " +
            "t1.tranFlowNo AS payJsTranFlowNo, " +
            "t1.fee AS payJsFee, " +
            "t1.cashAmt AS payJsCashAmt, " +
            "t1.firstAmt AS payJsFirstAmt, " +
            "t1.tradeTime AS payJsTradeTime, " +
            "t1.refundStas AS payRefundStas) " +
            "from SettleAccount t1,Jzdj t2,Zhsq t3,Order t4 " +
            " where t1.visitId = t2.bisJzdjLsh " +
            "   and t1.patientId = t3.bisZhsqId " +
            "   and t2.bisJzdjOrderId = t4.bisOrderId " +
            "   and (?1 IS NULL OR ?1 = '' OR t3.bisZhsqSnrySfz = ?1 ) ",
            countQuery = "select count(*) " +
                    "from SettleAccount t1,Jzdj t2,Zhsq t3,Order t4 " +
                    " where t1.visitId = t2.bisJzdjLsh " +
                    "   and t1.patientId = t3.bisZhsqId " +
                    "   and t2.bisJzdjOrderId = t4.bisOrderId " +
                    "   and (?1 IS NULL OR ?1 = '' OR t3.bisZhsqSnrySfz = ?1 ) ")
    Page<QueryRefundVo> findCityAndHotelAllSelf(String blurry, Pageable pageable);

    @Query(value = "select new me.zhengjie.business.domain.vo.KB03( " +
            "       t.aab034," +
            "       t.akb020," +
            "       t.akb021," +
            "       t.aae002," +
            "       t.aae015," +
            "       t.cjt100," +
            "       t.akb062," +
            "       t.ckb152," +
            "       t.ckb151," +
            "       t.ckb153," +
            "       t.akb068," +
            "       t.zkb060," +
            "       t.tsryzf," +
            "       t.ckb156," +
            "       t.ckb208," +
            "       t.yjbjdx," +
            "       t.akb064," +
            "       t.ckb161," +
            "       t.ckb169," +
            "       t.insuNo," +
            "       t.insuName," +
            "       t.id," +
            "       t.aae016) " +
            "       from Bfd t " +
            "       where 1=1 " +
            "       and (?1 IS NULL OR ?1 = '' OR t.akb020 = ?1 ) " +
            "       and (?2 IS NULL OR ?2 = '' OR t.aae002 = ?2 ) " +
            "       and (?3 IS NULL OR ?3 = '' OR t.insuNo = ?3 ) ",
            countQuery = "select count(1) from Bfd t  " +
                    "       where 1=1 " +
                    "       and (?1 IS NULL OR ?1 = '' OR t.akb020 = ?1 ) " +
                    "       and (?2 IS NULL OR ?2 = '' OR t.aae002 = ?2 ) " +
                    "       and (?3 IS NULL OR ?3 = '' OR t.insuNo = ?3 ) ")
    Page<KB03> queryKb03(String akb020, String aae002, String insuNo, Pageable pageable);



    @Query(value = "select new me.zhengjie.business.domain.vo.KB22( " +
            "       t.id," +
            "       t.akb020," +
            "       t.aae002," +
            "       t.aae015," +
            "       t.cjt100," +
            "       t.aab034," +
            "       t.aac001," +
            "       t.aac003," +
            "       t.aac002," +
            "       t.akc192," +
            "       t.akc194," +
            "       t.akc264," +
            "       t.akc260," +
            "       t.akc255," +
            "       t.akc261," +
            "       t.aaa005," +
            "       t.yl," +
            "       t.dqbo," +
            "       t.zkc024," +
            "       t.akc268," +
            "       t.kjsl," +
            "       t.hlkjje," +
            "       t.hckjje," +
            "       t.akc265," +
            "       t.ckc283," +
            "       t.adlTotal," +
            "       t.aae013," +
            "       t.aaa022) " +
            "       from Bfdmx t " +
            "       where 1=1 " +
            "       and (?1 IS NULL OR ?1 = '' OR t.akb020 = ?1 ) " +
            "       and (?2 IS NULL OR ?2 = '' OR t.aae002 like ?2||'%' ) " +
            "       and (?3 IS NULL OR ?3 = '' OR t.cjt100 = ?3 ) " +
            "       and (?4 IS NULL OR ?4 = '' OR t.aaa022 = ?4 ) " +
            "       and (?5 IS NULL OR ?5 = '' OR t.akb020 in (select distinct r.akb020 from Bfd r where r.insuNo = ?5) ) " +
            "       and (?6 IS NULL OR ?6 = '' OR t.aab034 = ?6 ) " +
            "       and (?7 IS NULL OR ?7 = '' OR t.aac002 like '%'||?7||'%' or t.aac003 like '%'||?7||'%' ) ",
            countQuery = "select count(1) from Bfdmx t  " +
                    "       where 1=1 " +
                    "       and (?1 IS NULL OR ?1 = '' OR t.akb020 = ?1 ) " +
                    "       and (?2 IS NULL OR ?2 = '' OR t.aae002 like ?2||'%' ) " +
                    "       and (?3 IS NULL OR ?3 = '' OR t.cjt100 = ?3 ) " +
                    "       and (?4 IS NULL OR ?4 = '' OR t.aaa022 = ?4 ) " +
                    "       and (?5 IS NULL OR ?5 = '' OR t.akb020 in (select distinct r.akb020 from Bfd r where r.insuNo = ?5) ) " +
                    "       and (?6 IS NULL OR ?6 = '' OR t.aab034 = ?6 ) " +
                    "       and (?7 IS NULL OR ?7 = '' OR t.aac002 like '%'||?7||'%' or t.aac003 like '%'||?7||'%' ) ")
    Page<KB22> queryKb22(String akb020, String aae002, String cjt100, String aaa022, String insuNo, String aab034, String ryxx, Pageable pageable);
}